package com.utils;

import java.sql.*;

public class DBUtil {
	
	protected static final String sqliteDRIVER = "org.sqlite.JDBC";
	protected static final String PromotionUrl = "jdbc:sqlite:Pendingurl.sdb";
	private static Connection conn = null;
	
	static {
		try {
			Class.forName(sqliteDRIVER);
			conn = DriverManager.getConnection(PromotionUrl);
		} catch (ClassNotFoundException e) {
			System.out.println("connection 异常1 : " + e.toString());
		} catch (SQLException e) {
			System.out.println("connection 异常2 : " + e.toString());
		}
	}
	
/**
 * insert into pendingUrl
 * 
 * @param table
 * @param url
 * @return
 */
	public static synchronized boolean insertPendingUrl(String table, String url) {
		boolean res = false;
//		if(isHaving("promotion", url))
//			return;
		String sql = "insert into " + table + " values (null, '" + url + "', datetime('now'))";
		try {
			Statement stat = conn.createStatement();
			stat.execute(sql);
			stat.close();
			res = true;
		} catch (SQLException e) {
			System.out.println("insertPendingUrl 异常 : " + e.toString());
		}
		return res;
	}
	
	public static synchronized boolean isHaving(String table, String url) {
		boolean tag = false;
		String sql = "select id from " + table + " where url='" + url + "';";
		try {
			Statement stat = conn.createStatement(); //
			ResultSet rs = stat.executeQuery(sql);
			if(rs.next()){
				tag = true; //2 false
//				System.out.println(url + "  sqlite exists...");
			}
			rs.close();
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("isHaving 异常 : " + e.toString());
		}
		return tag;
	}
	
	public static synchronized String[][] query(String sql) throws SQLException{
		Statement stat = conn.createStatement(); 
		ResultSet rs = stat.executeQuery(sql);
	    int numCols = rs.getMetaData().getColumnCount();
	    int array_size1 = 5000;
	    String[][] rr = new String[array_size1][numCols];

	    for (int row = 0; rs.next(); row++)
	    {
	      for (int i = 1; i <= numCols; i++) {
	        try
	        {
	          rr[row][(i - 1)] = ("" + rs.getString(i));
	        }
	        catch (ArrayIndexOutOfBoundsException e)
	        {
	          array_size1 *= 2;
	          String[][] rr1 = new String[array_size1][numCols];
	          System.arraycopy(rr, 0, rr1, 0, row);
	          rr = rr1;
	          rr[row][(i - 1)] = ("" + rs.getString(i));
	        }

	      }
	    }

      rs.close();
      stat.close();
      
	    return rr;
	} 
	
	public static void closeConn(){
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 */
	public static void execDB(String sql) {
		try {
			Statement stat = conn.createStatement();
			stat.execute(sql);
			stat.close();
		} catch (SQLException e) {
			System.out.println("execDB 异常 : " + e.toString());
		}
	}
	
	public static void main(String[] argv) throws SQLException {
		String sql = "select * from BBSURL";
//		 String sql = "CREATE TABLE [BBSURL] ([id] INTEGER  PRIMARY KEY NOT NULL,[URL] VARCHAR(200)  UNIQUE NOT NULL,[updatetime] TIMESTAMP  NULL)";
		 String[][] test = query(sql);
		 System.out.println(test.length);
	}
	
}
